package com.gugee.tiktok.common.model.dal.blogger;

import com.gugee.tiktok.common.model.dto.SlimBloggerDto;
import com.gugee.tiktok.common.model.mysql.BloggerMain;
import com.gugee.tiktok.data.common.msg.BloggerMessage;
import org.springframework.data.jdbc.repository.query.Modifying;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Optional;

@Repository
public interface BloggerMainRepository extends CrudRepository<BloggerMain, Long>, BloggerMainExtRepository {
    boolean existsByoUid(long oUid);
    @Modifying
    @Query("UPDATE blogger_main c SET last_pub_time=:bloggerClTime,tiktoker_status=:tiktokerStatus WHERE c.o_uid = :oUid  ")
    int updateBloggerStatusByoUid(@Param("bloggerClTime") Date bloggerClTime, @Param("tiktokerStatus") int tiktokerStatus, @Param("oUid") long oUid);


    @Modifying
    @Query("UPDATE blogger_main  SET fans_portrait_update=:updateTime WHERE o_uid = :oUid  ")
    int updateBloggerFansPortraitUpdate(long updateTime,long oUid);
    /**
     * 获取星图播主作为消息发送
     * @param start
     * @param count
     * @return
     */
    @Query("SELECT  b.id,a.core_user_id AS uid,b.o_region AS region FROM " +
            " blogger_market a LEFT JOIN blogger_main b ON a.core_user_id = b.o_uid " +
            " ORDER BY a.id  " +
            " LIMIT :start,:count")
    List<BloggerMessage> findMarkerBloggerMessagesRange(long start, long count);
    /**
     * 不分类且按粉丝数排序查询播主
     * @param start
     * @param count
     * @return
     */
//    @Query("SELECT id,o_uid as uid,o_region as region FROM blogger_main " +
//            " WHERE o_follower_count>= :followerCount " +
//            " AND o_region != 'IN' " +
//            " ORDER BY o_follower_count DESC " +
//            " LIMIT :start,:count")
//    List<BloggerMessage> findBloggerMessagesForFollowerCountAndCount(int followerCount,long start, long count);

//    @Query( "SELECT id, uid, region,last_pub_time FROM ("+
//            "SELECT id,o_uid as uid,o_region as region,last_pub_time FROM blogger_main " +
//            " WHERE o_follower_count>= :followerCount " +
//            " AND o_region != 'IN' AND tiktoker_status=0 " +
//            " ORDER BY o_follower_count DESC " +
//            " LIMIT :start,:count ) b " +
//            " WHERE (UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(last_pub_time))/60/60>:intervalHours")
//    List<BloggerMessage> findBloggerMessagesForFollowerCountAndCount(int followerCount,long start, long count,int intervalHours);
    @Query(
            "SELECT id,o_uid as uid,o_region as region FROM blogger_main " +
            " WHERE o_follower_count>= :followerCount " +
            " AND o_region != 'IN' AND tiktoker_status=0 AND (UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(last_pub_time))/60/60>:intervalHours " +
            " ORDER BY o_follower_count DESC " +
            " LIMIT :start,:count  ")
    List<BloggerMessage> findBloggerMessagesForFollowerCountAndCount(int followerCount,long start, long count,int intervalHours);

    @Query(
            "SELECT id,o_uid as uid,o_region as region FROM blogger_main " +
            " WHERE o_follower_count>= :followerCount " +
            " AND o_region in (:regions) AND tiktoker_status=0 AND (UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(last_pub_time))/60/60>:intervalHours " +
            " ORDER BY o_follower_count DESC " +
            " LIMIT :start,:count  ")
    List<BloggerMessage> findBloggerMessagesForFollowerCountAndCount(int followerCount,long start, long count,int intervalHours,@Param("regions")List<String> regions);



    @Query("SELECT id,o_uid as uid,o_region as region FROM blogger_main " +
                    " WHERE o_follower_count>= :followerCount " +
                    " AND o_region != 'IN' AND tiktoker_status=0 AND (UNIX_TIMESTAMP(now())-fans_portrait_update)/60/60>:intervalHours " +
                    " ORDER BY o_follower_count DESC " +
                    " LIMIT :start,:count  ")
    List<BloggerMessage> findBloggerMessagesForFollowerCountAndFansPortrait(int followerCount,long start, long count,int intervalHours);

    @Query("SELECT id,o_uid as uid,o_region as region FROM blogger_main " +
                    " WHERE o_follower_count>= :followerCount " +
                    " AND o_region in (:regions) AND tiktoker_status=0 AND (UNIX_TIMESTAMP(now())-fans_portrait_update)/60/60>:intervalHours " +
                    " ORDER BY o_follower_count DESC " +
                    " LIMIT :start,:count  ")
    List<BloggerMessage> findBloggerMessagesForFollowerCountAndFansPortrait(int followerCount,long start, long count,int intervalHours,@Param("regions")List<String> regions);
    /**
     * 分类且按分数排序 查询播主，
     * @param type
     * @param top
     * @return
     */
    @Query(" select id, uid, region,blogger_type, ranking from (" +
            "SELECT id,o_uid as uid,o_region as region,blogger_type,'true' as ranking ,last_pub_time FROM blogger_main " +
            " where o_follower_count>= :followerCount AND blogger_type = :type "+
            " AND o_region != 'IN'  AND tiktoker_status=0  " +
            " ORDER BY score DESC " +
            "LIMIT 0,:top" +
            ") p where (UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(last_pub_time))/60/60 >:intervalHours"
    )
    List<BloggerMessage> findBloggerMessagesByBloggerTypeSortDesc(int followerCount,String type, long top,int intervalHours);

    @Query("SELECT id,o_uid as uid,o_region as region FROM blogger_main " +
            "WHERE  o_follower_count>= :followerCount AND id > :id " +
            "AND o_region != 'IN'" +
            "ORDER BY id ASC " +
            "LIMIT :count")
    List<SlimBloggerDto> findSlimBloggersByIdAndCount(int followerCount,long id, int count);



    @Query("SELECT id,o_uid as uid,o_region as region FROM blogger_main " +
            "WHERE  o_follower_count>= :followerCount AND o_region != 'IN'   AND tiktoker_status=0  " +
            "ORDER BY o_follower_count DESC " +
            "LIMIT :start,:count")
    List<SlimBloggerDto> findSlimBloggersFollowerAndCount(int followerCount,int start, int count);

    @Query("SELECT id,o_uid as uid,o_region as region FROM blogger_main " +
            "WHERE  o_follower_count>= :followerCount AND o_region != 'IN' " +
            "ORDER BY score DESC " +
            "LIMIT :start,:count")
    List<SlimBloggerDto> findSlimBloggersScoreAndCount(int followerCount,int start, int count);
//    @Query("SELECT id,o_uid as uid,o_region as region FROM blogger_main " +
//            "WHERE o_follower_count  >= :followers and o_region != 'IN' " +
//            "ORDER BY score DESC " +
//            "LIMIT :start,:count")
//    List<SlimBloggerDto> findSlimBloggersScoreAndOFollower(int followers, int start, int count);
    @Query("SELECT * FROM blogger_main " +
            "WHERE o_region != 'IN'" +
            "ORDER BY score DESC " +
            "LIMIT :start,:count")
    List<BloggerMain> findBloggersByPageAndCount(int start, int count);

//    @Query("SELECT * FROM blogger_main " +
//            "WHERE o_follower_count  >= :followerCount AND id > :id " +
//            "AND o_region != 'IN'" +
//            "ORDER BY id ASC " +
//            "LIMIT :count")
//    List<BloggerMain> findBloggersByIdAndCount(int followerCount,long id, int count);

    @Query("SELECT id,o_uid as uid,o_region as region FROM blogger_main WHERE blogger_type != '';")
    List<SlimBloggerDto> findAuditSlimBloggers();

    @Query("SELECT blogger_type FROM blogger_main WHERE o_uid = :oUid")
    String findTypeByoUid(long oUid);

    Optional<BloggerMain> findByoUid(long oUid);

    List<BloggerMain> findByoUniqueIdIn(Collection<String> oUniqueIds);

    Optional<BloggerMain> findByoUniqueId(String oUniqueId);

    List<BloggerMain> findByoUidIn(List<Long>  ids);

    /**
     * 更新博主性别、年龄、来源字段
     * @param aiAge
     * @param aiGender
     * @param genderSource
     * @param uid
     * @return
     */
    @Modifying
    @Query("UPDATE blogger_main t set t.ai_age = :aiAge,t.ai_gender = :aiGender,t.gender_source = :genderSource where t.o_uid = :uid ")
    int updateAiGenderAndAiAgeByoUid(@Param("aiAge") int aiAge, @Param("aiGender") int aiGender,
                                     @Param("genderSource") int genderSource,@Param("uid") long uid);
}
